package com.css.bjdt_core.importExport.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.importExport.entity.DataExportXml;
import com.css.bjdt_core.reportFill.entity.CellDataValue;
import com.css.bjdt_core.util.CommonUtil;
import com.css.bjdt_core.util.ReportConstant;

@Service
public class DataExportXmlService {

	@Autowired
	private Dao dao;

	public List<DataExportXml> getATemplateDataXml(String annualId, String templateId, String dzzId, String summary,
			String dzzName) {
		List<DataExportXml> dataExportXmlEndList = new ArrayList<DataExportXml>();
		//先加表头
		DataExportXml dataExportXml = new DataExportXml();
		dataExportXml.setTag("H001");
		dataExportXml.setVal(dzzName);
		dataExportXmlEndList.add(dataExportXml);
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		Sql strSql = null;
		//汇总单位和非汇总单位分开,先搞表内区域的
		if (summary.equals("1")) {
			strSql = Sqls.create("select CELL_VALUE,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID from "
					+ tableName
					+ " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and SUM_STATUS = @summary and CELL_TYPE in ('1','2','3')"
					+ " and CELL_VALUE != '0' and CELL_VALUE is not null and REL_ROW_NUM is not null and REL_COL_NUM is not null order by REL_COL_NUM,REL_ROW_NUM");
			strSql.params().set("dzzId", dzzId).set("templateId", templateId).set("summary", summary);
		} else {
			strSql = Sqls.create("select CELL_VALUE,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID from "
					+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and CELL_TYPE in ('1','2','3')"
					+ " and CELL_VALUE != '0' and CELL_VALUE is not null and REL_ROW_NUM is not null and REL_COL_NUM is not null order by REL_COL_NUM,REL_ROW_NUM");
			strSql.params().set("dzzId", dzzId).set("templateId", templateId);
		}

		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<CellDataValue> cellDataValueList = new ArrayList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValueList.add(cellDataValue);
				}
				return cellDataValueList;
			}
		});

		dao.execute(strSql);
		List<CellDataValue> cdv = strSql.getList(CellDataValue.class);

		for (CellDataValue cellDataValue : cdv) {
			DataExportXml dataExportXmlInside = new DataExportXml();
			String tabVal = CommonUtil.numToLetter(cellDataValue.getRelColNum()) + "" + cellDataValue.getRelRowNum();
			dataExportXmlInside.setTag(tabVal);
			dataExportXmlInside.setVal(cellDataValue.getCellValue());
			dataExportXmlEndList.add(dataExportXmlInside);
		}

		//再查询表外的，然后将表外的也赋值进去
		Sql strSqlOutSide = null;
		if (summary.equals("1")) {
			strSqlOutSide = Sqls.create("select CELL_VALUE,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID from "
					+ tableName
					+ " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and SUM_STATUS = @summary and CELL_TYPE in ('1','2','3')"
					+ " and REL_ROW_NUM is null and REL_COL_NUM is null order by to_number(ABS_ROW_NUM),to_number(ABS_COL_NUM)");
			strSqlOutSide.params().set("dzzId", dzzId).set("templateId", templateId).set("summary", summary);
		} else {
			strSqlOutSide = Sqls.create("select CELL_VALUE,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID from "
					+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and CELL_TYPE in ('1','2','3')"
					+ " and REL_ROW_NUM is null and REL_COL_NUM is null order by to_number(ABS_ROW_NUM),to_number(ABS_COL_NUM)");
			strSqlOutSide.params().set("dzzId", dzzId).set("templateId", templateId);
		}
		strSqlOutSide.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSqlOutSide) throws SQLException {
				List<CellDataValue> cellDataValueList = new ArrayList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValueList.add(cellDataValue);
				}
				return cellDataValueList;
			}
		});
		dao.execute(strSqlOutSide);
		List<CellDataValue> cdvOutSide = strSqlOutSide.getList(CellDataValue.class);
		int a = 1;
		for (CellDataValue cellDataValue : cdvOutSide) {
			if (cellDataValue.getCellValue().equals("0") || null == cellDataValue.getCellValue()) {
				a++;
				continue;
			} else {
				DataExportXml dataExportXmlOutside = new DataExportXml();
				DecimalFormat df = new DecimalFormat("000");
				String tabNumber = df.format(a);
				dataExportXmlOutside.setTag("T" + tabNumber);
				dataExportXmlOutside.setVal(cellDataValue.getCellValue());
				dataExportXmlEndList.add(dataExportXmlOutside);
				a++;
			}

		}
		return dataExportXmlEndList;
	}
}
